package com.shop.dao;

import java.sql.SQLException;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.shop.domain.Product;
import com.shop.utils.JDBCUtils;

public class ProductDao {

	public void insert(Product product) {
		String sql = "insert into products values(?,?,?,?,?,?)";
		Object[] param = { JDBCUtils.getProductID(), product.getName(), product.getPrice(), product.getCategory(),
				product.getDescription(), product.getImg() };
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		try {
			queryRunner.update(sql, param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Product> findAll() {
		String sql = "select * from products";
		try {
			QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
			List<Product> list = runner.query(sql, new BeanListHandler<Product>(Product.class));
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Product findProduct(String id) {
		String sql = "select * from products where id = ?";
		try {
			QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
			Product product = runner.query(sql, new BeanHandler<Product>(Product.class), id);
			return product;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	public List<Product> findAll(int start, int pageNo) {
		String sql = "select * from products limit ?, ?";
		try {
			QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
			List<Product> products = runner.query(sql, new BeanListHandler<Product>(Product.class), start, pageNo);
			return products;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public int getTotalNum() {
		String sql = "select count(*) from products";
		try {
			QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
			return runner.query(sql, new ScalarHandler<Integer>(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

}
